Skip Headers

Oracle® Database 2 Day DBA 10g Release 2 (10.2) Part Number B14196-02 |
Home | Book List | Contents | Index | Master Index | Feedback

Previous | Next
| View PDF

# Administering Database Users

You can use Enterprise Manager to administer database users. You can create specific users to enable access to the database based on roles and privileges. For example, you might create a specific user MYUSER with the necessary privileges (which you can assign to roles) to develop applications. You might create other users specifically to administer the database.

In the Users & Privileges section of the Administration home page, click Users. The Users page appears, as shown in Figure 7-2. From this page you can view, add, edit, or delete database users.

Figure 7-2 Users Page

Description of "Figure 7-2 Users Page"

## Creating Users

In this section, you create a user named `MYUSER`, set the password, and assign `MYUSER` to the `USERS` tablespace. This series of tasks enables `myuser` to log in to the database. Later, you learn how to edit and assign roles to this user.

To create a database user, perform the following tasks as user `SYS` or `SYSTEM`:

1. On the Users page, click Create.

   The Create Users General page shown in Figure 7-3 is displayed. The General page is one of a series of pages in the Users property page.

   Figure 7-3 Create User General Page

   Description of "Figure 7-3 Create User General Page"

2. In the Name box, enter a user name. For example, enter `MYUSER`.

3. In the Profile list, accept the value `DEFAULT`. The profile specifies the resource limit.

   Note that this profile does not allow the user to log in to Enterprise Manager, which would require the user to be assigned the `DBA` role. For more information, see "Administering Roles".

4. In the Authentication list, accept the value `Password`. For advanced authentication schemes, see Oracle Database Security Guide.

5. In the Enter Password and Confirm Password boxes, enter your password.

6. Do not check Expire Password now. If the account status to set to expired, then the user or the database administrator must change the password before the user can log in to the database.

7. For the Default Tablespace field, click the flashlight icon and select the `USERS` tablespace. While users can specify different defaults for each user, it is easiest to define a default permanent tablespace and a default temporary tablespace at the database level, instead of the user level. For more information on the `USERS` tablespace, see "Some Tablespaces in the Database".

8. For the Temporary Tablespace field, click the flashlight icon and select the `TEMP` tablespace. For more information on the `TEMP` tablespace, see "Some Tablespaces in the Database".

9. In Status, select Unlocked.

   You can later lock an account to keep a user out of the database. Locking an account is preferable to deleting a user, which removes all associated tables and data.

10. Click OK.

    The Users page appears. You can now see an entry for the new user `myuser`.

The Users General page has links to several other property pages. For example, you can specify user roles, privileges, quotas, consumer groups, and proxy users. Consumer groups are groups of users, or sessions, that are grouped together based on their processing needs.

Note: This task creates a regular database user. To grant the user system administrator privileges, complete additional steps described in Chapter 3, "Getting Started with Oracle Enterprise Manager".

See Also: Oracle Database Administrator's Guide and Oracle Database Security Guide for more information on roles, privileges, quotas, consumer groups and proxy authentication

## Using a Shortcut to Create Users

If you later want to create other users similar to user `MYUSER`, or another existing user, Oracle provides a shortcut.

To use a shortcut to create a new user:

1. Select the user that you want to duplicate. For example, select `MYUSER`.

2. From the Actions menu, select Create Like.

3. Click Go.

   The Create User General page appears. This page displays a new user with the same attributes as the duplicated user.

The Actions list also provides shortcuts for other actions, as well as providing a means to display the SQL DDL used to create a user.

## Editing Users

You can change the user attributes by navigating to the Users page (Figure 7-2) and clicking Edit. You can then select the properties page with the attributes that you want to change.

### Changing the Tablespace Quota for a User

When you create a new user, the user is not able to save data until given a tablespace quota. This procedure enables user `MYUSER` to save data.

To change the tablespace quotas assigned to user `MYUSER`:

1. From the Users page, select `MYUSER` in the results list, then click Edit.

   The Edit User General page appears.

2. Click Quotas to display the Quotas property page.

   This user has 0 MB quota on all tablespaces, which means that `MYUSER` has no quota in any tablespace. Because `MYUSER` belongs to the `USERS` tablespace, he must have quota to create tables and other schema objects in this tablespace.

3. In the Quota list for tablespace `USERS`, select Value.

4. In the corresponding Value column, enter `100`.

5. Click Apply.

   User `MYUSER` can now create objects in the `USERS` tablespace.

6. Click the Database tab to return to the Administration home page.

### Unlocking Accounts and Resetting Passwords

Locked accounts cannot be accessed by the user. During installation, you can unlock and reset the Oracle-supplied database user accounts. If you did not choose to unlock those accounts at that time, then you can do so now.

To unlock database account:

1. From the Users page shown in Figure 7-2, select a user whose Account Status is shown as `EXPIRED AND LOCKED`. For example, select `DMSYS`.

2. From the Actions list, select Unlock User and click Go.

3. Click Yes to the confirm that you want to unlock the user.

   This action unlocks the user account. The Account Status is now `EXPIRED`. This action does not reset the password, so the user is still unable to log in to the database.

4. From the Users page, select the same user and click Edit.

   The Edit User General page appears.

5. Enter a password for the account and click Apply.

You must follow the preceding steps individually for each account that you want to unlock and reset.

Locking an account is similar to unlocking it. You select the user and then choose Lock User from the Actions list. Locking an account denies access to the account.

To better understand the ramifications of unlocking and resetting accounts, see the other property pages available on the Edit user page. Specifically, click the Roles, System Privileges, and Object Privileges links to see the privileges of the user whose account you are enabling.

### Granting Roles

You can use Enterprise Manager to grant roles to users. For example, you can grant a user the `DBA` role, which allows the user to administer the database. You can also grant user-created roles, such as the `APPDEV` role you created in "Creating Roles".

To grant the `DBA` and `APPDEV` role to `MYUSER`:

1. In the Users page, select `MYUSER` and click Edit.

   The Edit User: `MYUSER` page appears.

2. Click Roles.

   A page appears with the list of roles for `MYUSER`. The only role should be `CONNECT`.

3. Click Edit List.

   The Modify Roles page appears.

4. In the Available Roles list, select the `DBA` and click Move to add it to the Selected Roles list. Do the same for the `APPDEV` role. You can also move the role by double-clicking.

5. Click OK.

   The Roles property page appears.

6. Click Apply to save your changes.

### Revoking Roles

You can also revoke roles from users.

To revoke the `DBA` role from `MYUSER`:

1. In the Users page, select `MYUSER` and click Edit.

   The Edit User: `MYUSER` page appears.

2. Click Roles.

   A page appears with the list of roles for `MYUSER`.

3. Click Edit List.

   The Modify Roles page appears.

4. In the Selected Roles list, select the `DBA` role and click Remove to make it part of the Available Roles list.

5. Click OK.

   The Roles property page.

6. Click Apply to save your changes.

## Dropping Users

Enterprise Manager enables you to drop users. You must exercise caution when dropping users, however, because this action drops all schema objects owned by the user including tables and indexes.To deny user access to the database, it is better to lock the user account or expire the user password. See "Unlocking Accounts and Resetting Passwords".

To drop `MYUSER`:

1. In the Users page, select `MYUSER`.

2. Click Delete.

   A confirmation page appears.

3. Click Yes to confirm the deletion.